<?php if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class Arearscount_model extends CI_Model {
	public function __construct() {
		parent::__construct();
	}

	public function get_zones(){
		$q = $this->db->get('ar_zone1_values_v');
		return $q->result();
	}

	public function get_arrears($one,$two,$area){
		$sql;
		if($area == 'ALL'){
			$sql = ""; 
		}else{
			$sql = " AND aea.zone_1 = '".$area."' "; 
		}
		$q = $this->db->query("SELECT tbl.area,tbl.name,tbl.num,tbl.a_count FROM (
										SELECT aea.zone_1 area
										  ,aa.acct_num num
										  ,aa.acct_name name
									      ,count(x.customer_id) a_count
									  FROM ar_account aa
									  INNER JOIN ar_entity_address aea
									  		  ON aa.acct_addr = aea.address_id
									  LEFT JOIN (SELECT customer_id FROM ubs_invoices_payments_v WHERE ifnull(sum_pay,0) != ifnull(invoice_total,0)) x ON x.customer_id = aa.account_id
									  WHERE aa.enable_flag = 'Y'
									  	".$sql."
										AND LAST_DAY(aa.active_start)
									    BETWEEN LAST_DAY(aa.active_start)
									    AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
									    GROUP BY aea.zone_1,aa.acct_num,aa.acct_name 
									) tbl
									WHERE a_count BETWEEN '".$one."' AND '".$two."'
									GROUP BY area,name,num
									ORDER BY tbl.a_count ASC");
		return $q->result();
	}

	public function get_arrears_dis($area){
		$sql;
		if($area == 'ALL'){
			$sql = ""; 
		}else{
			$sql = "WHERE area = '".$area."' "; 
		}
		$q = $this->db->query("SELECT tbl.area,tbl.name,tbl.num,tbl.a_count FROM (
										SELECT aea.zone_1 area
										  ,aa.acct_num num
										  ,aa.acct_name name
									      ,count(x.customer_id) a_count
									  FROM ar_account aa
									  INNER JOIN ar_entity_address aea
									  		  ON aa.acct_addr = aea.address_id
									  LEFT JOIN (SELECT customer_id FROM ubs_invoices_payments_v WHERE ifnull(sum_pay,0) != ifnull(invoice_total,0)) x ON x.customer_id = aa.account_id
									  WHERE aa.status = 'INACT' OR aa.status = 'N'
									    GROUP BY aea.zone_1,aa.acct_num,aa.acct_name
									) tbl
									".$sql."
									GROUP BY area,name,num
									ORDER BY tbl.a_count ASC");
		return $q->result();
	}

	public function get_pnd($id){

		$q = $this->db->query("SELECT aea.zone_1 area, aa.acct_num num, aa.acct_name name, uipv.invoice_id invoice_id, uipv.invoice_date invoice_date, (ifnull(uipv.invoice_total,0) - ifnull(uipv.sum_pay,0)) balance, uipv.due_date FROM ubs_invoices_payments_v uipv
								INNER JOIN ar_account aa ON aa.account_id = uipv.customer_id
							    INNER JOIN ar_entity_address aea ON aea.address_id = aa.acct_addr 
							    WHERE ifnull(sum_pay,0) != ifnull(invoice_total,0)
							    AND aa.acct_num = '".$id."'
							    ORDER BY uipv.invoice_date ASC");
		return $q->result();
	}

	public function getzoning($zone) {
		$this->db->where('zone_code', $zone);
		$query = $this->db->get('ar_zones');
		return $query->result();
	}
	/*public function get_pnd($id){
		$q = $this->db->query("SELECT aea.zone_1 area, aa.acct_num num, aa.acct_name name, uipv.invoice_id invoice_id, uipv.invoice_date invoice_date, (ifnull(						uipv.invoice_total,0) - ifnull(uipv.sum_pay,0)) balance FROM ubs_invoices_payments_v uipv
								INNER JOIN ar_account aa ON aa.account_id = uipv.customer_id
							    INNER JOIN ar_entity_address aea ON aea.address_id = aa.acct_addr 
							    WHERE ifnull(sum_pay,0) != ifnull(invoice_total,0)
							    AND aa.acct_num = ".$id."
							    AND aa.enable_flag = 'Y'
							    AND aa.status = 'ACT'
								AND LAST_DAY(aa.active_start)
							    BETWEEN LAST_DAY(aa.active_start)
							    AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
							    ORDER BY uipv.invoice_date ASC");
		return $q->result();
	}*/
}